/*******************************************************************************
 * FireCall_Multi.sql
 * SeaWorld Parks & Entertainment
 * FTCSEAP6505
 */
SET IMPLICIT_TRANSACTIONS OFF
SET CURSOR_CLOSE_ON_COMMIT OFF
SET NOCOUNT ON
USE DBA
GO
DECLARE @serverdb TABLE (servername SYSNAME NOT Null, dbname SYSNAME NOT Null)	-- INSERT INTO @serverdb VALUES ('','')
DECLARE @notfound TABLE ([Server Not Found] SYSNAME NOT Null)
DECLARE
  @CMRNum		VARCHAR(16),
  @server		SYSNAME,
  @db			SYSNAME,
  @login		VARCHAR(32),
  @pw			VARCHAR(16),
  @duration		SMALLINT,
  @previous		SYSNAME,
  @rv			INT,
  @tsql_command	VARCHAR(MAX)

--==============================================================================
-- REQUIRED VALUES --
SET @CMRNum		= ''
SET @Duration	= 1440	-- 24h=1440;48h=2880
INSERT INTO @serverdb (servername,dbname) VALUES ('','')


-- Validation
;WITH s AS (SELECT DISTINCT servername FROM @serverdb)
INSERT INTO @notfound ([Server Not Found]) SELECT
  s.servername
	FROM s LEFT JOIN master.dbo.sysservers ss ON (s.servername = ss.srvname)
	WHERE ss.srvid IS Null
IF 0 < @@ROWCOUNT BEGIN
  SELECT * FROM @notfound
  GOTO EndScript
END

-- Set Derived Values
SET @previous= ''
IF @CMRNum = '' SET @CMRNum= CONVERT(CHAR(8),GETDATE(),112)+RIGHT('0'+CAST(DATEPART(Hh,GETDATE()) AS VARCHAR(2)),2)+RIGHT('0'+CAST(DATEPART(n,GETDATE()) AS VARCHAR(2)),2)+RIGHT('0'+CAST(DATEPART(s,GETDATE()) AS VARCHAR(2)),2)
SELECT @login= 'SQLFireCall' + RIGHT(@CMRNum, 6), @previous= ''
EXECUTE DBA.dbo.uspCreatePassword_Out 3, 3, 3, 0, @pw OUTPUT

-- Here's Where Things Happen
PRINT 'FireCall Login Created'
PRINT 'Login: ' + @login
PRINT 'Duration: ' + RIGHT('0'+CAST(@duration/60 AS VARCHAR(2)),2)+'h'+RIGHT('0'+CAST(@duration%60 AS VARCHAR(2)),2)+'m beginning '+ REPLACE(CAST(GETDATE() AS VARCHAR(19)),SPACE(2),SPACE(1)) + RIGHT(CAST(SYSDATETIMEOFFSET() AS VARCHAR(34)),7)
DECLARE list CURSOR LOCAL FAST_FORWARD FOR SELECT servername, dbname FROM @serverdb
OPEN list
FETCH NEXT FROM list INTO @server, @db
WHILE @@FETCH_STATUS = 0 BEGIN
  IF @server = @previous BEGIN
	SET @tsql_command= 'EXECUTE [' + @server + '].[' + @db + ']..sp_grantdbaccess ''' + @Login + '''; '
		+ 'EXECUTE [' + @server + '].[' + @db + ']..sp_addrolemember ''db_datareader'', ''' + @Login + '''; '
		+ 'EXECUTE [' + @server + '].[' + @db + ']..sp_addrolemember ''db_datawriter'', ''' + @Login + ''''
	EXECUTE (@tsql_command)
	SET @rv= @@ERROR
  END
  ELSE EXECUTE @rv= DBA.dbo.DBA_FireCall_WI @server, @db, @login, @pw, @duration, @CMRNum, 0, Null
  PRINT '[' + @server + '].[' + @db + ']' + CHAR(9) + CASE @rv WHEN 0 THEN 'Ok' ELSE 'ERROR' END
  SET @previous= @server
  FETCH NEXT FROM list INTO @server, @db
END
CLOSE list
DEALLOCATE list
PRINT 'Password will be sent separately.'
PRINT 'Password: ' + @pw

EndScript:
SET NOCOUNT OFF